package com.jingyanzi.finance.util;

import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.OutputStream;  
import java.util.ArrayList;  
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;  
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;  
import java.util.regex.Pattern;  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;  
import org.apache.poi.hssf.usermodel.HSSFComment;  
import org.apache.poi.hssf.usermodel.HSSFFont;  
import org.apache.poi.hssf.usermodel.HSSFPatriarch;  
import org.apache.poi.hssf.usermodel.HSSFRichTextString;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.hssf.util.HSSFColor;

import com.jingyanzi.finance.query.KeyValueDO;

/** 
 *  
 * @author cl
 * @version v1.0 
 * @param <T> 
 *            应用泛型，代表任意一个符合javabean风格的类 
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() 
 */  
public class ExportExcel<T>  
{  
    public void exportExcel(Collection<T> dataset, OutputStream out)  
    {  
        exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");  
    }  
    public void exportExcel(String[] headers, Collection<T> dataset,  
            OutputStream out)  
    {  
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");  
    }  
    public void exportExcel(String[] headers, Collection<T> dataset,  
            OutputStream out, String pattern)  
    {  
        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);  
    }  
    @SuppressWarnings("unchecked")  
    public void exportExcel(String title, String[] headers,  
            Collection<T> dataset, OutputStream out, String pattern)  
    {  
        // 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);  
        // 设置表格默认列宽度为15个字节  
        sheet.setDefaultColumnWidth((short) 15);  
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();  
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.VIOLET.index);  
        font.setFontHeightInPoints((short) 12);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // 把字体应用到当前的样式  
        style.setFont(font);  
        // 生成并设置另一个样式  
        HSSFCellStyle style2 = workbook.createCellStyle();  
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        // 生成另一个字体  
        HSSFFont font2 = workbook.createFont();  
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
        // 把字体应用到当前的样式  
        style2.setFont(font2);  
  
        // 声明一个画图的顶级管理器  
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
        // 定义注释的大小和位置,详见文档  
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,  
                0, 0, 0, (short) 4, 2, (short) 6, 5));  
        // 设置注释内容  
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));  
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.  
        comment.setAuthor("leno");  
  
        // 产生表格标题行  
        HSSFRow row = sheet.createRow(0);  
        for (short i = 0; i < headers.length; i++)  
        {  
        	
        	
            HSSFCell cell = row.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);  
        }
        
        
  
        // 遍历集合数据，产生数据行  
        Iterator<T> it = dataset.iterator();  
        int index = 0;  
        while (it.hasNext())  
        {  
            index++;  
            row = sheet.createRow(index);  
           // T t = (T) it.next();  
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            
            Map<String, String> m=(Map<String, String>) it.next();
            
            int j=0;
            for(String s:m.keySet()) {
            	
            	HSSFCell cell = row.createCell(j);
            	cell.setCellStyle(style2); 
            	String textValue = m.get(s);
            	
            	 if (textValue != null)  
                {  
                     Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
                     Matcher matcher = p.matcher(textValue);  
                     if (matcher.matches())  
                     {  
                         // 是数字当作double处理  
                         cell.setCellValue(Double.parseDouble(textValue));  
                     }  
                     else  
                     {  
                         HSSFRichTextString richString = new HSSFRichTextString(  
                                 textValue);
                         HSSFFont font3 = workbook.createFont();  
                         font3.setColor(HSSFColor.BLUE.index);  
                         richString.applyFont(font3);  
                         cell.setCellValue(richString);  
                     }  
                 }
            	
            	j++;
            }
            
            
           
        }  
        try  
        {  
            workbook.write(out);  
        }  
        catch (IOException e)  
        {  
            e.printStackTrace();  
        }  
    }  
  
    public static void main(String[] args)  
    {  
    	List<Map<String, String>> list=new ArrayList<Map<String, String>>();
    	
    	String a[]= {"姓名","成绩","描述"};
    	
    	for(int i=0;i<10;i++) {
    		
    		Map<String, String> map=new HashMap<String, String>();
    		map.put("成绩", "60");
    		map.put("姓名", "程林"+i);
    		map.put("ddd", "xxx");
    		list.add(map);
    	}
    	ExportExcel.exportE(list, a, "E://chenglin.xls");
          
    	System.out.println("over");
    }
    /**
     * @param data  需要导出的数据
     * @param headers  数据的表头
     * @param url  存储的路径
     * @return
     */
    public static boolean exportE(List<Map<String, String>> data,String [] headers,String url) {
    	
    	ExportExcel<Map<String, String>> ex=new ExportExcel<Map<String, String>>();
    	
    	List<Map<String, String>> dataset=new ArrayList<Map<String, String>>();
    	
    	for(Map<String, String> map:data) {
    		dataset.add(map);
    	}
    	
    	 try {
			OutputStream out = new FileOutputStream(url);
			
			ex.exportExcel(headers, dataset, out);
			
			out.close();
			 
			 return true;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
    	return false;
    }
    
    public static boolean export(List<KeyValueDO> l1,List<KeyValueDO> l2,String path) {
		
		String a[]= {"收款类型","金钱","支付方式","金额"};
		
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		
		for(int i=0;i<l1.size()||i<l2.size();i++) {
			Map<String, String> map=new HashMap<String, String>();
			if(i<l1.size()) {
				KeyValueDO k = l1.get(i);
				map.put("收款类型", "￥"+k.getValue());
				map.put("金钱", k.getKey());
			}else {
				map.put("收款类型", "");
				map.put("金钱", "");
			}
			if(i<l2.size()) {
				KeyValueDO k = l2.get(i);
				map.put("支付方式", k.getKey());
				map.put("金额", "￥"+k.getValue());
			}else {
				map.put("支付方式", "");
				map.put("金额", "");
			}
			list.add(map);
		}
		ExportExcel.exportE(list, a, path);
		return true;
	}
}  